/**
 * ToDo List:
 * Delete big files In commit
 * Rewrite 'submit'
 * Add Auth
 */

const path = require('path');
const log4js = require('log4js');
const config = require('./config');
log4js.configure({
    appenders: {
        "allLogs": {
            type: 'dateFile',
            filename: __dirname + '/log/all',
            pattern: 'yyyy-MM-dd.log',
            alwaysIncludePattern: true,
            encoding: 'utf-8',
            backups: 2,
        },
        "infoLogs": {
            type: 'file',
            filename: __dirname +  '/log/info.log',
            encoding: 'utf-8',
        },
        "email": {
            type: "@log4js-node/smtp",
            transport: {
                plugin: 'smtp',
                options: {
                    host: config.host,
                    port: config.port,
                    auth: {
                        user: config.user,
                        pass: config.pass
                    }
                }
            },
            sender: config.user,
            subject: 'Error Report - Logger',
            recipients: config.to,
            html: true,
        },
    },
    categories: {
        "default": {
            appenders: ["allLogs"],
            level: "all",
        },
        "yslog": {
            appenders: ["allLogs"],
            level: "all",
        },
        "Sqlite3": {
            appenders: ["allLogs"],
            level: "all",
        },
        "info": {
            appenders: ["infoLogs"],
            level: "info",
        },
        'err':{
            appenders: ['email'],
            level: 'error'
        }
    }
});

var allLogger = log4js.getLogger('yslog');
var infoLogger = log4js.getLogger('info');
var errLogger = log4js.getLogger('err');
/**
 * @function 写入Log数据
 * @param {String} level 级别 TRACE<DEBUG<INFO<WARN<ERROR<FATAL<MARK
 * @param {String} msg 日志内容
 * @returns {Boolean}  FLAG
 */
function writeLog(level, msg){
    switch(level){
        case 'TRACE':
                allLogger.trace(msg);
                break;
        case 'DEBUG':
                allLogger.debug(msg);
                break;
        case 'INFO':
                allLogger.info(msg);
                infoLogger.info(msg);
                break;
        case 'WARN':
                allLogger.warn(msg);
        case 'ERROR':
                allLogger.error(msg);
                errLogger.error(msg);
        case 'FATAL':
                allLogger.fatal(msg);
                errLogger.fatal(msg);
        case 'MARK':
                allLogger.mark(msg);
                errLogger.mark(msg);
        default:
            return false;
    }
    console.log('[' + level + '] ' + msg);
    return true;
}

var dbLogger = log4js.getLogger('Sqlite3');
/**
 * Write Database log to file.
 * @param {String} msg 
 */
function dbLog(msg) {
    dbLogger.trace(msg);
    console.log(`[SQLite3] ${msg}`);
}

const Database = require("better-sqlite3");
const fs = require('fs');

// 检测 databases 文件夹并创建
if (!fs.existsSync('./databases')){
    fs.mkdirSync('./databases');
}


/* 用户系统 */
writeLog('TRACE', `连接 _user.db`);
userdb = new Database(path.join(__dirname, `/databases/_user.db`), {verbose: dbLog});
const userTableExist = userdb.prepare("SELECT name FROM sqlite_master WHERE type = 'table' AND name = ? COLLATE NOCASE"); // 检查表是否存在
if (!userTableExist.get('user')){
    userdb.exec("CREATE TABLE user( \
        ID  INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL, \
        account_name TEXT  NOT NULL, \
        user_name TEXT  NOT NULL, \
        email TEXT  NOT NULL, \
        password_SHA256 TEXT  NOT NULL \
    )");
}


const crypto = require('crypto');
var jwt = require('jsonwebtoken');
function calcSHA256(input) {
    const hash = crypto.createHash('sha256');
    hash.update(input);
    return hash.digest('hex');
}


class YSLog {
    login(account_name, password){
        var password_SHA256 = calcSHA256(password);
        var userData = userdb.prepare("SELECT * FROM user WHERE account_name = ?").get(account_name);
        if (userData && password_SHA256 == userData.password_SHA256){
            this.userData = userData;
            var token = jwt.sign({ userID: userData['ID'] }, config.JWT_SECRET_KEY/*, { expiresIn: '7d' }*/);
            return {
                "code": 200,
                "success": 1,
                'msg': '登录成功',
                "token": token
            };
        }else{
            return {
                "code": 200,
                "success": 0,
                "msg": '登录失败'
            };
        }
    }

    close(){
        if(this.db){
            writeLog('TRACE', `断开 ${this.userData.account_name}.db`);
            this.db.close();
        }
    }

    /**
     * 验证 token
     * @param {String} token 
     * @returns {boolean} 是否成功
     */
    verify(token){
        if(!token) return false;
        try{
            const decoded = jwt.verify(token, config.JWT_SECRET_KEY);
            // JWT 验证成功，decoded 是 JWT 的负载
            this.userData = userdb.prepare("SELECT * FROM user WHERE ID = ?").get(decoded.userID);

            writeLog('TRACE', `连接 ${this.userData.account_name}.db`);
            this.db = new Database(path.join(__dirname, `/databases/${this.userData.account_name}.db`), {verbose: dbLog});
            return true;
        }catch (err){
            return false;
        }
    }

    resetPassword(original_password, new_password){
        var original_password_SHA256 = calcSHA256(original_password);
        var userData = userdb.prepare("SELECT * FROM user WHERE account_name = ?").get(this.userData.account_name);
        if (userData && original_password_SHA256 == userData.password_SHA256){
            var new_password_SHA256 = calcSHA256(new_password);
            userdb.prepare('UPDATE user SET password_SHA256 = ? WHERE ID = ?;').run(new_password_SHA256, this.userData.ID);
            return true;
        }else{
            return false;
        }
    }

    /**
     * 获取监督者列表
     */
    getSupervisors(){
        return userdb.prepare('SELECT * FROM user WHERE ID != ?').all(this.userData.ID);
    }

    getUserData(){
        return this.userData;
    }



    inputFilter(reqQuery){
        if (!reqQuery.quantity || reqQuery.quantity == '') reqQuery['quantity'] = 'NULL';
        if (!reqQuery.progress || reqQuery.progress == '') reqQuery['progress'] = 'NULL';
        if (!reqQuery.taskNote || reqQuery.taskNote == '') reqQuery['taskNote'] = '';
        if (!reqQuery.logNote || reqQuery.logNote == '') reqQuery['logNote'] = '';
        return reqQuery;
    }


    getAllTask(){
        return this.db.prepare("SELECT * FROM task").all();
    }

    /**
         * @param {Boolean} doneFlag 0 or 1
         * @returns {Query}
         */
    getTaskIDByDoneFlag(doneFlag){
        return this.db.prepare("SELECT ID FROM task WHERE doneFlag = ?").all(doneFlag);
    }

    getLogByToday(){
        return this.db.prepare("SELECT * FROM log WHERE date = strftime('%Y.%m.%d', 'now', '+8 hours')").all();
    }

    get_log_by_end_NULL(){
        return this.db.prepare("SELECT * FROM log WHERE end IS NULL").get();
    }

    getLogByDate(date){
        return this.db.prepare("SELECT * FROM log WHERE date = ?").all(date);
    }

    getLogByTaskID_Date(taskID, date){
        return this.db.prepare("SELECT * FROM log WHERE taskID = ? AND date = ?").all(taskID, date);
    }


    /**
     * @param {query} reqQuery {category, summary, quantity, progress, note, doneFlag}
     */
    createTask(reqQuery){
        reqQuery = this.inputFilter(reqQuery);
        
        this.db.prepare(`INSERT INTO task (category, summary, quantity, note, doneFlag, targetFlag) VALUES ('${reqQuery.category}', '${reqQuery.summary}', ${reqQuery.quantity}, '${reqQuery['taskNote']}', 0, 50)`).run();
        return;
    }

    updateTask(reqQuery){
        reqQuery = this.inputFilter(reqQuery);
        
        this.db.prepare("UPDATE task SET progress = " + reqQuery['progress'] + ", note = '" + reqQuery['taskNote'] + "' WHERE ID = " + reqQuery['taskID']).run();
        return;
    }

    /**
     * @param {query} logData {date, start, taskID, quantity, note}
     * @returns {number} logID
     */
    insertLog(logData){
        logData = this.inputFilter(logData);

        this.db.prepare(`INSERT INTO log (date, start, taskID, quantity, note) VALUES (strftime('%Y.%m.%d', 'now', '+8 hours'), strftime('%H: %M', 'now', '+8 hours'), ${logData.taskID}, ${logData.quantity}, '${logData['logNote']}')`).run();

        return this.db.prepare("SELECT seq FROM sqlite_sequence WHERE name = 'log'").get()['seq'];
    }

    /**
     * @param {query} logData {logID, quantity, note}
     */
    updateLog(logData){
        logData = this.inputFilter(logData);

        this.db.prepare(`UPDATE log SET quantity = ${logData.quantity}, note = '${logData['logNote']}' WHERE ID = ${logData.logID}`).run();
    }

    archiveLog(logData){
        logData = this.inputFilter(logData);

        this.db.prepare(`UPDATE log SET end = strftime('%H: %M', 'now', '+8 hours'), quantity = '${logData.quantity}', note = '${logData['logNote']}' WHERE ID = ${logData.logID}`).run();
        
        if (logData['doneFlag'] == 'on') this.db.prepare(`UPDATE task SET doneFlag = 1 WHERE ID = ${logData['taskID']}`).run();
    }

    getIndexData(){
        var result = {};
        result['user_name'] = this.userData.user_name;
        result['doneTaskID'] = this.getTaskIDByDoneFlag(1);
        result['todoTaskID'] = this.getTaskIDByDoneFlag(0);
        result['log'] = this.getLogByToday();
        
        result['task'] = {};
        this.getAllTask().map((p) => {
            result.task[p.ID] = p}
        );

        result['notice'] = config.notice;

        return result;
    }

    getAllLogData(selData){
        var result = {};

        result['log'] = {};
        var logIndex;
        if (selData.taskID && selData.taskID != '') {
            logIndex = this.db.prepare(`SELECT DISTINCT date FROM log WHERE taskID = ${selData.taskID} ORDER BY date DESC`).all();
            for(var i = 0, len = logIndex.length; i < len; ++i) {
                result.log[logIndex[i]['date']] = this.getLogByTaskID_Date(selData['taskID'], logIndex[i].date);
            }
        } else {
            const get_date_from_log = this.db.prepare("SELECT DISTINCT date FROM log ORDER BY date DESC");
            logIndex = get_date_from_log.all();
            for(var i = 0, len = logIndex.length; i < len; ++i) {
                result.log[logIndex[i]['date']] = this.getLogByDate(logIndex[i].date);
            }
        }
        result['date'] = logIndex;
        //var logIndex = this.db.prepare("SELECT DISTINCT date FROM log ORDER BY date DESC");

        result['task'] = {};
        this.getAllTask().map((p) => {
            result.task[p.ID] = p
        });

        //console.log(result);
        return result;
    }

    getUnarchivedLog(){
        var get_log_by_end_NULL = this.db.prepare("SELECT * FROM log WHERE end IS NULL");
        return get_log_by_end_NULL.get();
    }

    getLogDataByID(selData){
        if(!selData['logID']){
            throw "[400] Error: Bad Request.";
        }

        return this.db.prepare("SELECT * FROM log WHERE ID = ?").get(selData['logID']);
    }

    getTaskDataByID(selData){
        if(!selData['taskID']){
            throw "[400] Error: Bad Request.";
        }

        return this.db.prepare("SELECT * FROM task WHERE ID = ?").get(selData['taskID']);
    }
    
    /**
     * @param {query} selData {taskID, targetFlag}
     */
    setTaskTargetFlagByID(selData){
        this.db.prepare(`UPDATE task SET targetFlag = ${selData['targetFlag']} WHERE ID = ${selData['taskID']}`).run();
        return;
    }

    getCurrentLog(){
        //select content from topic order by id desc
        return this.db.prepare('SELECT * FROM log ORDER BY ID DESC').get();
    }
}


module.exports = YSLog;